import pymysql
from time import time, strftime, localtime
from pathlib import Path


class method:
    # 数字修改为16位银行卡号
    def generateNum(self, updata_num):
        while True:
            if len(str(updata_num)) == 16:
                break
            elif len(str(updata_num)) > 16:
                updata_num = updata_num - 100000000000000
            else:
                updata_num = updata_num + 100000000000000
        return updata_num

    # 选择跳过的用例
    def skipTest(self, cases, skip_test_index):
        '''
        t = []
        # 获取其中的test方法
        for i in the_class:
            if "test" in i:
                t.append(i)
        '''
        # 创建一个列表用来存储选择，有多少方法就准备多少元素
        skip_test = []
        for j in range(cases):
            skip_test.append(0)
        # 遍历选择列表，将跳过的方法选项改为1
        if skip_test_index != []:
            for k in skip_test_index:
                skip_test[k - 1] = 1
        return skip_test

    # 报错截图保存路径
    def failedScreenshotNamed(self, test_name):
        filename1 = ("%s.jpg" % test_name)
        st = strftime('%Y-%m-%d-%H-%M-%S', localtime(time()))
        i = 1
        scrpath = 'D:\pythonProject\ 理财自动化测试\报错截图'  # 指定的保存目录
        capturename = '\\' + test_name + '--' + st + '.png'  # 自定义命名截图
        wholepath = scrpath + capturename
        if Path(scrpath).is_dir():  # 判断文件夹路径是否已经存在
            pass
        else:
            Path(scrpath).mkdir()  # 如果不存在，创建文件夹
            wholepath = scrpath + capturename
        return wholepath

    # 通过用户名清理数据库
    def cleanDatabaseByUsername(self, username):
        # 1.连接
        con = pymysql.connect(host="127.0.0.1", user="root", password="root", database="finance")

        # 2.创建控制台
        cursor = con.cursor()

        # 3.准备一条sql语句: 增，删，改
        # sql = "insert into  people  values('jason',56,5623.31,'s001')"
        sql = "delete from user where username = %s"  # 模板占位符
        param = [username]

        # 4.执行
        cursor.execute(sql, param)  # (模板，具体填充的数据)

        # 5.提交到数据库
        con.commit()

        # 6.关闭资源
        cursor.close()
        con.close()

    # 通过用户名清理管理员
    def cleanAdminByUsername(self, username):
        # 1.连接
        con = pymysql.connect(host="127.0.0.1", user="root", password="root", database="finance")

        # 2.创建控制台
        cursor = con.cursor()

        # 3.准备一条sql语句: 增，删，改
        # sql = "insert into  people  values('jason',56,5623.31,'s001')"
        sql = "delete from admin where username = %s"  # 模板占位符
        param = [username]

        # 4.执行
        cursor.execute(sql, param)  # (模板，具体填充的数据)

        # 5.提交到数据库
        con.commit()

        # 6.关闭资源
        cursor.close()
        con.close()

    # 通过银行清理银行卡
    def cleanCardByBank(self, bank):
        # 1.连接
        con = pymysql.connect(host="127.0.0.1", user="root", password="root", database="finance")

        # 2.创建控制台
        cursor = con.cursor()

        # 3.准备一条sql语句: 增，删，改
        # sql = "insert into  people  values('jason',56,5623.31,'s001')"
        sql = "delete from bankcard where cardBank = %s"  # 模板占位符
        param = [bank]

        # 4.执行
        cursor.execute(sql, param)  # (模板，具体填充的数据)

        # 5.提交到数据库
        con.commit()

        # 6.关闭资源
        cursor.close()
        con.close()

    # 通过卡号清理银行卡
    def cleanCardByNum(self, num):
        # 1.连接
        con = pymysql.connect(host="127.0.0.1", user="root", password="root", database="finance")

        # 2.创建控制台
        cursor = con.cursor()

        # 3.准备一条sql语句: 增，删，改
        sql = "delete from bankcard where cardNum = %s"  # 模板占位符
        param = [num]

        # 4.执行
        cursor.execute(sql, param)  # (模板，具体填充的数据)

        # 5.提交到数据库
        con.commit()

        # 6.关闭资源
        cursor.close()
        con.close()

    # 通过卡号清理银行卡
    def cleanCardById(self, id):
        # 1.连接
        con = pymysql.connect(host="127.0.0.1", user="root", password="root", database="finance")

        # 2.创建控制台
        cursor = con.cursor()

        # 3.准备一条sql语句: 增，删，改
        sql = "delete from bankcard where userId = %s"  # 模板占位符
        param = [id]

        # 4.执行
        cursor.execute(sql, param)  # (模板，具体填充的数据)

        # 5.提交到数据库
        con.commit()

        # 6.关闭资源
        cursor.close()
        con.close()

    # 查询用户名
    def findUsername(self, name):
        # 1.连接
        con = pymysql.connect(host="127.0.0.1", user="root", password="root", database="finance")

        # 2.创建控制台
        cursor = con.cursor()

        # 3.准备一条sql语句: 增，删，改
        sql = "select  *  from user  where username = %s"
        param = [name]

        # 4.执行
        cursor.execute(sql, param)  # (模板，具体填充的数据)

        # 4.1提取查询的数据
        data = cursor.fetchall()
        # print(data)

        # 5.提交到数据库
        con.commit()

        # 6.关闭资源
        cursor.close()
        con.close()
        return data

    # 查询管理员
    def findAdmin(self, name):
        # 1.连接
        con = pymysql.connect(host="127.0.0.1", user="root", password="root", database="finance")

        # 2.创建控制台
        cursor = con.cursor()

        # 3.准备一条sql语句: 增，删，改
        sql = "select  *  from admin where username = %s"
        param = [name]

        # 4.执行
        cursor.execute(sql, param)  # (模板，具体填充的数据)

        # 4.1提取查询的数据
        data = cursor.fetchall()
        # print(data)

        # 5.提交到数据库
        con.commit()

        # 6.关闭资源
        cursor.close()
        con.close()
        return data

    # 添加用户名
    def addUsername(self, name):
        # 1.连接
        con = pymysql.connect(host="127.0.0.1", user="root", password="root", database="finance")

        # 2.创建控制台
        cursor = con.cursor()

        # 3.准备一条sql语句: 增，删，改
        sql = "insert into user(username,password,paypwd) values (%s,%s,%s)"
        param = [name, "e10adc3949ba59abbe56e057f20f883e", "666666"]

        # 4.执行
        cursor.execute(sql, param)  # (模板，具体填充的数据)

        # 5.提交到数据库
        con.commit()

        # 6.关闭资源
        cursor.close()
        con.close()

    # 添加管理员
    def addAdmin(self, name):
        # 1.连接
        con = pymysql.connect(host="127.0.0.1", user="root", password="root", database="finance")

        # 2.创建控制台
        cursor = con.cursor()

        # 3.准备一条sql语句: 增，删，改
        sql = "insert into admin(username,password,status) values (%s,%s,%s)"
        param = [name, "e10adc3949ba59abbe56e057f20f883e", 1]

        # 4.执行
        cursor.execute(sql, param)  # (模板，具体填充的数据)

        # 5.提交到数据库
        con.commit()

        # 6.关闭资源
        cursor.close()
        con.close()

    # 添加无支付密码用户名
    def addUsernameWithOutPayPwd(self, name):
        # 1.连接
        con = pymysql.connect(host="127.0.0.1", user="root", password="root", database="finance")

        # 2.创建控制台
        cursor = con.cursor()

        # 3.准备一条sql语句: 增，删，改
        sql = "insert into user(username,password) values (%s,%s)"
        param = [name, "e10adc3949ba59abbe56e057f20f883e"]

        # 4.执行
        cursor.execute(sql, param)  # (模板，具体填充的数据)

        # 5.提交到数据库
        con.commit()

        # 6.关闭资源
        cursor.close()
        con.close()

    # 添加银行卡
    def addBankcard(self, num):
        # 1.连接
        con = pymysql.connect(host="127.0.0.1", user="root", password="root", database="finance")

        # 2.创建控制台
        cursor = con.cursor()

        # 3.准备一条sql语句: 增，删，改
        sql = "insert into bankcard(cardNum) values (%s)"
        param = [num]

        # 4.执行
        cursor.execute(sql, param)  # (模板，具体填充的数据)

        # 5.提交到数据库
        con.commit()

        # 6.关闭资源
        cursor.close()
        con.close()

    # 添加完整银行卡
    def addCompleteBankcard(self, bank, num, id, balance):
        # 1.连接
        con = pymysql.connect(host="127.0.0.1", user="root", password="root", database="finance")

        # 2.创建控制台
        cursor = con.cursor()

        # 3.准备一条sql语句: 增，删，改
        sql = "insert into bankcard(cardBank,type,cardNum,userId,balance) values (%s,%s,%s,%s,%s)"
        param = [bank, 1, num, id, balance]

        # 4.执行
        cursor.execute(sql, param)  # (模板，具体填充的数据)

        # 5.提交到数据库
        con.commit()

        # 6.关闭资源
        cursor.close()
        con.close()

    # 设置默认支付卡
    def setDefaultPayCard(self, id, num):
        # 1.连接
        con = pymysql.connect(host="127.0.0.1", user="root", password="root", database="finance")

        # 2.创建控制台
        cursor = con.cursor()

        # 3.准备一条sql语句: 增，删，改
        sql = "update bankcard set defaultl=0 where userid=%s"
        param = [id]

        # 4.执行
        cursor.execute(sql, param)  # (模板，具体填充的数据)

        sql = "UPDATE bankcard SET defaultl=1 WHERE cardNum=%s"
        param = [num]
        # 4.执行

        cursor.execute(sql, param)  # (模板，具体填充的数据)
        # 5.提交到数据库
        con.commit()

        # 6.关闭资源
        cursor.close()

    # 通过银行卡号修改卡类型
    def setDefaultPayCard2(self, num):
        # 1.连接
        con = pymysql.connect(host="127.0.0.1", user="root", password="root", database="finance")

        # 2.创建控制台
        cursor = con.cursor()
        con.close()
        # 3.准备一条sql语句: 增，删，改
        sql = "UPDATE bankcard SET defaultl=1 WHERE cardNum=%s"
        param = [num]
        # 4.执行

        cursor.execute(sql, param)  # (模板，具体填充的数据)

        # 5.提交到数据库
        con.commit()

        # 6.关闭资源
        cursor.close()

    # 通过银行卡号修改卡类型
    def setCardTypeByNum(self, type, num):
        # 1.连接
        con = pymysql.connect(host="127.0.0.1", user="root", password="root", database="finance")

        # 2.创建控制台
        cursor = con.cursor()

        # 3.准备一条sql语句: 增，删，改
        sql = "UPDATE bankcard SET type=%s WHERE cardNum=%s"
        # sql = "insert into bankcard(type,cardNum) values (%s,%s)"
        param = [type, num]

        # 4.执行
        cursor.execute(sql, param)  # (模板，具体填充的数据)

        # 5.提交到数据库
        con.commit()

        # 6.关闭资源
        cursor.close()
        con.close()

    # 通过id修改用户信誉

    def setReputationById(self, id):
        # 1.连接
        con = pymysql.connect(host="127.0.0.1", user="root", password="root", database="finance")

        # 2.创建控制台
        cursor = con.cursor()

        # 3.准备一条sql语句: 增，删，改
        sql = "UPDATE user SET reputation=%s WHERE id=%s"
        # sql = "insert into bankcard(type,cardNum) values (%s,%s)"
        param = ["良好", id]

        # 4.执行
        cursor.execute(sql, param)  # (模板，具体填充的数据)

        # 5.提交到数据库
        con.commit()

        # 6.关闭资源
        cursor.close()
        con.close()

    # 通过id修改用户真名
    def setRealnameById(self, real_name, id):
        # 1.连接
        con = pymysql.connect(host="127.0.0.1", user="root", password="root", database="finance")

        # 2.创建控制台
        cursor = con.cursor()

        # 3.准备一条sql语句: 增，删，改
        sql = "UPDATE user SET realname=%s WHERE id=%s"
        param = [real_name, id]

        # 4.执行
        cursor.execute(sql, param)  # (模板，具体填充的数据)

        # 5.提交到数据库
        con.commit()

        # 6.关闭资源
        cursor.close()
        con.close()

    # 通过用户名查找id
    def findIdByUserame(self, username):
        # 1.连接
        con = pymysql.connect(host="127.0.0.1", user="root", password="root", database="finance")

        # 2.创建控制台
        cursor = con.cursor()

        # 3.准备一条sql语句: 增，删，改
        sql = "select  id  from user  where username = %s"
        param = [username]

        # 4.执行
        cursor.execute(sql, param)  # (模板，具体填充的数据)

        # 4.1获取数据
        data = cursor.fetchone()

        # 5.提交到数据库
        con.commit()

        # 6.关闭资源
        cursor.close()
        con.close()
        return data

    # 通过用户id查询银行卡号
    def findBankcardById(self, id):
        # 1.连接
        con = pymysql.connect(host="127.0.0.1", user="root", password="root", database="finance")

        # 2.创建控制台
        cursor = con.cursor()

        # 3.准备一条sql语句: 增，删，改
        sql = "select  cardNum from bankcard  where userId = %s"
        param = id

        # 根据 id查询银行卡
        cursor.execute(sql, param)

        data = cursor.fetchall()
        # 5.提交到数据库
        con.commit()

        # 6.关闭资源
        cursor.close()
        con.close()
        return data

    # 通过卡号查询银行卡
    def findBankcardByNum(self, num):
        # 1.连接
        con = pymysql.connect(host="127.0.0.1", user="root", password="root", database="finance")

        # 2.创建控制台
        cursor = con.cursor()

        # 3.准备一条sql语句: 增，删，改
        sql = "select * from bankcard  where cardNum = %s"
        param = num

        # 根据 id查询银行卡
        cursor.execute(sql, param)

        data = cursor.fetchall()
        # 5.提交到数据库
        con.commit()

        # 6.关闭资源
        cursor.close()
        con.close()
        return data

    # 通过卡号查询银行卡类型
    def findCartypeByNum(self, num):
        # 1.连接
        con = pymysql.connect(host="127.0.0.1", user="root", password="root", database="finance")

        # 2.创建控制台
        cursor = con.cursor()

        # 3.准备一条sql语句: 增，删，改
        sql = "select type from bankcard  where cardNum = %s"
        param = num

        # 根据 id查询银行卡
        cursor.execute(sql, param)

        data = cursor.fetchall()
        # 5.提交到数据库
        con.commit()

        # 6.关闭资源
        cursor.close()
        con.close()
        return data

    # 通过卡号查询银行卡id
    def findCardidByNum(self, num):
        # 1.连接
        con = pymysql.connect(host="127.0.0.1", user="root", password="root", database="finance")

        # 2.创建控制台
        cursor = con.cursor()

        # 3.准备一条sql语句: 增，删，改
        sql = "select id from bankcard  where cardNum = %s"
        param = [num]

        # 根据 id查询银行卡
        cursor.execute(sql, param)

        data = cursor.fetchall()
        # 5.提交到数据库
        con.commit()

        # 6.关闭资源
        cursor.close()
        con.close()
        return data

    # 通过银行卡id查询卡号
    def findCardnumByCardid(self, id):
        # 1.连接
        con = pymysql.connect(host="127.0.0.1", user="root", password="root", database="finance")

        # 2.创建控制台
        cursor = con.cursor()

        # 3.准备一条sql语句: 增，删，改
        sql = "select cardnum from bankcard  where id = %s"
        param = [id]

        # 根据 id查询银行卡
        cursor.execute(sql, param)

        data = cursor.fetchall()
        # 5.提交到数据库
        con.commit()

        # 6.关闭资源
        cursor.close()
        con.close()
        return data

    # 通过银行卡号查询余额
    def findBalanceByCardNum(self, num):
        # 1.连接
        con = pymysql.connect(host="127.0.0.1", user="root", password="root", database="finance")

        # 2.创建控制台
        cursor = con.cursor()

        # 3.准备一条sql语句: 增，删，改
        sql = "select balance from bankcard  where cardnum = %s"
        param = [num]

        # 根据 id查询银行卡
        cursor.execute(sql, param)

        data = cursor.fetchall()
        # 5.提交到数据库
        con.commit()

        # 6.关闭资源
        cursor.close()
        con.close()
        return data
